package com.moleq.service;

import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.moleq.db.DBError;
import com.moleq.db.DatabaseHelper;
import com.moleq.db.DatabaseSchema;
import com.moleq.db.DatabaseSchema.ItemSetting;
import com.moleq.dialog.MessageBox;
import com.moleq.models.PriceSetting;


public class PriceService {
	
	private DatabaseHelper DBHelper;
	private String errorString;
	private Context mContext;
	public static final int ALL = 0;
	public static final int UPC = 1;
	public static final int Department = 2;
	public static final int SubDept = 3;
	public static final int Vendor = 4;
	
	
	public PriceService() {

	}

	public PriceService(Context context) {
		this.DBHelper = new DatabaseHelper(context);
		mContext = context;
	}
	
	public PriceSetting getitemprice(String upc){
		
		PriceSetting obj = null;
		SQLiteDatabase db = DBHelper.getWritableDatabase();
		
		Cursor cursor = db.rawQuery("select "+ItemSetting.UNIT_PRICE +","+ ItemSetting.CURRENT_PRICE_QTY +",* from t_item_setting as b left join t_price_setting as a on a.F01 = b.F01 where b.F01 = '"+upc+"'", null);
		if(cursor == null){
			MessageBox.show(mContext,DBError.error);
			DBError.error = "";
			return obj;
		}
		while (cursor.moveToNext()) {
			
			String supc = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PriceSetting.ITEM_UPC));
			String sPriceMultiple = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PriceSetting.PRICE_MULTIPLE));
			String sPrice = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PriceSetting.PRICE));
			String sStartdate = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PriceSetting.STAR_TDATE));
			String sStarttime = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PriceSetting.STAR_TTIME));
			String sEnddate = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PriceSetting.END_DATE));
			String sEndtime = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PriceSetting.END_TIME));
			String sPackagepricemultiple = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PriceSetting.PACKAGEPRICE_MULTIPLE));
			String sPackageprice = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PriceSetting.PACKAGE_PRICE));
			String sLimitedqtyamount = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PriceSetting.LIMITEDQTY_AMOUNT));
			String sLimitedqtyprice = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PriceSetting.LIMITEDQTY_PRICE));
			String sDiscountamount = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PriceSetting.DISCOUNT_AMOUNT));
			String sDiscountpercent = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PriceSetting.DISCOUNT_PERCENT));
			String sPricemm = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PriceSetting.PRICE_MM));
			String screatetime = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PriceSetting.CREATE_DATETIME));
			String supdatetime = cursor.getString(cursor.getColumnIndex(DatabaseSchema.PriceSetting.UPDATE_DATETIME));
			String scurrentqty = cursor.getString(cursor.getColumnIndex(DatabaseSchema.ItemSetting.CURRENT_PRICE_QTY));
			String scurrentprice = cursor.getString(cursor.getColumnIndex(DatabaseSchema.ItemSetting.UNIT_PRICE));
			String discountlink =  cursor.getString(cursor.getColumnIndex(DatabaseSchema.PriceSetting.DISCOUNT_LINK));
			String pricelvl =  cursor.getString(cursor.getColumnIndex(DatabaseSchema.PriceSetting.PRICE_LVL));
			
//			if((supc == null || supc == "") && (sPriceMultiple == null || sPriceMultiple == "") && (sPrice == null || sPrice == "")
//					&& (sStartdate == null || sStartdate == "")	&& (sStarttime == null || sStarttime == "") && (sEnddate == null || sEnddate == "")
//				&& (sEndtime == null || sEndtime == "") && (sPackagepricemultiple == null || sPackagepricemultiple == "") &&(sPackageprice == null || sPackageprice == "")
//				&& (sLimitedqtyamount == null || sLimitedqtyamount == "") && (sLimitedqtyprice == null || sLimitedqtyprice == "") && (sDiscountpercent == null || sDiscountpercent == "")
//				&& (sPricemm == null || sPricemm == "") && (discountlink == null || discountlink == "")){
//				
//			}else{
//				obj = new PriceSetting(supc, sPriceMultiple, sPrice, sStartdate, sStarttime, sEnddate,
//						sEndtime, sPackagepricemultiple, sPackageprice,sLimitedqtyamount,sLimitedqtyprice,sDiscountamount,sDiscountpercent,sPricemm
//						,scurrentqty,scurrentprice,screatetime,supdatetime,discountlink);
//			}
			
			obj = new PriceSetting(supc, sPriceMultiple, sPrice, sStartdate, sStarttime, sEnddate,
					sEndtime, sPackagepricemultiple, sPackageprice,sLimitedqtyamount,sLimitedqtyprice,sDiscountamount,sDiscountpercent,sPricemm
					,scurrentqty,scurrentprice,screatetime,supdatetime,discountlink,pricelvl);
			
			
		}

		cursor.close();
		return obj;
	}
	
	public boolean isExists(String upc){
		boolean lb = false;
		SQLiteDatabase db = DBHelper.getWritableDatabase();
		String sql = "select count(*) from t_price_setting where F01 = '"+upc+"'";
		Cursor cursor = db.rawQuery(sql, null);	
		if (cursor.moveToNext()) {
			if(cursor.getInt(0) > 0) lb = true;
		}
		cursor.close();
		return lb;
	}
	
	public boolean InsertOrUpdatePrice(PriceSetting p){
		boolean check = false;
		if (isExists(p.getUpc())) {
			check = this.UpdatePrice(p);
		}else {
			check = this.InsertPrice(p);
		}
		return check;
	}
	
	public boolean UpdatePrice(PriceSetting p){
//		SQLiteDatabase db = DBHelper.getWritableDatabase();
		boolean check = true;
		StringBuilder sb = new StringBuilder();
		sb.append(" UPDATE " + DatabaseSchema.PriceSetting.SCHEMA_NAME  );
		sb.append( " set " + DatabaseSchema.PriceSetting.DISCOUNT_AMOUNT + " = ?, ");
		sb.append( " " + DatabaseSchema.PriceSetting.DISCOUNT_PERCENT + " = ? ");
		sb.append( " where " + DatabaseSchema.PriceSetting.ITEM_UPC + " = ? ");
		
		errorString = DBHelper.execSQL(sb.toString(),new Object[]{ p.getDiscountamount(),p.getDiscountpercent(),p.getUpc()});

		if (errorString.length() > 0) {
			check = false;
		} 
		return check;
//		db.execSQL("UPDATE " + DatabaseSchema.PriceSetting.SCHEMA_NAME + " set "+ DatabaseSchema.PriceSetting.DISCOUNT_AMOUNT+" = ?,F136 = ?,F137 = ?," +
//				"F144 = ?,F138 = ?,F145 = ?,F143 = ?,F139 = ?,F147 = ?,F148 = ?,F111 = ?," +
//				"F112 = ?,F32 = ?,update_datetime = strftime('%Y%m%d%H%M%S','now') where F01 = ?",
//				new Object[] { p.getPriceMultiple(), p.getPrice(), getdatetime(p.getStartdate()),
//				gettime(p.getStarttime()), getdatetime(p.getEnddate()), gettime(p.getEndtime()),
//				p.getPackagepricemultiple(), p.getPackageprice(), p.getLimitedqtyamount(),
//				p.getLimitedqtyprice(), p.getDiscountamount(), p.getDiscountpercent(), p.getPricemm(),
//				p.getUpc()});
	}
	
	public boolean InsertPrice(PriceSetting p){
//		SQLiteDatabase db = DBHelper.getWritableDatabase();
		boolean check = true;
//		db.execSQL("INSERT INTO " + DatabaseSchema.PriceSetting.SCHEMA_NAME + " values(?,?,?,?,?,?,?,?,?,?," +
//				"?,?,?,?,strftime('%Y%m%d%H%M%S','now'),strftime('%Y%m%d%H%M%S','now'))",
//				new Object[] { p.getUpc(), p.getPriceMultiple(), p.getPrice(), getdatetime(p.getStartdate()),
//				gettime(p.getStarttime()), getdatetime(p.getEnddate()), gettime(p.getEndtime()),
//				p.getPackagepricemultiple(), p.getPackageprice(), p.getLimitedqtyamount(),
//				p.getLimitedqtyprice(), p.getDiscountamount(), p.getDiscountpercent(),
//				p.getPricemm()});
		
		errorString = DBHelper.execSQL("INSERT INTO " + DatabaseSchema.PriceSetting.SCHEMA_NAME + " values(?,?,?,?,?,?,?,?,?,?," +
				"?,?,?,?,strftime('%Y%m%d%H%M%S','now'),strftime('%Y%m%d%H%M%S','now'),?,?)",
				new Object[] { p.getUpc(), p.getPriceMultiple(), p.getPrice(), getdatetime(p.getStartdate()),
				gettime(p.getStarttime()), getdatetime(p.getEnddate()), gettime(p.getEndtime()),
				p.getPackagepricemultiple(), p.getPackageprice(), p.getLimitedqtyamount(),
				p.getLimitedqtyprice(), p.getDiscountamount(), p.getDiscountpercent(),
				p.getPricemm(),p.getDislink(),p.getPrice_lvl()});

		if (errorString.length() > 0) {
			check = false;
		} 
		return check;
	}
	
	
	public PriceSetting getitempricesetting(String upc) {
//		SQLiteDatabase db = DBHelper.getWritableDatabase();
		PriceSetting item = null;
		String sql = "select * from t_price_setting where F01 = '" + upc + "'";
		Cursor cursor = DBHelper.rawQuery(sql, null);
		if(cursor == null){
			MessageBox.show(mContext,DBError.error);
			DBError.error = "";
			return item;
		}		
//		if (cursor == null) {
////			ShowMessage(DBError.error, mMessageDialog.MESSAGE_BOX_OK,
////					mMessageDialog.ICON_ERROR);
//			DBError.error = "";
//			return null;
//		}
		if (cursor.moveToNext()) {
			item = new PriceSetting();
			item.setUpc(cursor.getString(0));
			String ls = cursor.getString(2);
			double ll = cursor.getDouble(2);
			item.setPriceMultiple(cursor.getString(1));

			item.setPrice(cursor.getString(2));
			item.setStartdate(cursor.getString(3));
			item.setStarttime(cursor.getString(4));
			item.setEnddate(cursor.getString(5));
			item.setEndtime(cursor.getString(6));
			item.setPackagepricemultiple(cursor.getString(7));
			item.setPackageprice(cursor.getString(8));
			item.setLimitedqtyamount(cursor.getString(9));
			item.setLimitedqtyprice(cursor.getString(10));
			item.setDiscountamount(cursor.getString(11));
			item.setDiscountpercent(cursor.getString(12));
			item.setPricemm(cursor.getString(13));
			item.setCreateTime(cursor.getString(14));
			item.setUpdateTime(cursor.getString(15));
			item.setDislink(cursor.getString(16));
			item.setPrice_lvl(cursor.getString(17));
		} else {
			item = null;
		}
		cursor.close();
		return item;
	}

	public Boolean checkitemprice(String upc) {
		boolean lb = false;
		SQLiteDatabase db = DBHelper.getWritableDatabase();
		String sql = "select F01 from t_price_setting where F01 = '" + upc
				+ "'";
		Cursor cursor = db.rawQuery(sql, null);
		if (cursor == null) {
//			ShowMessage(DBError.error, mMessageDialog.MESSAGE_BOX_OK,
//					mMessageDialog.ICON_ERROR);
			MessageBox.show(mContext,DBError.error);
			DBError.error = "";
			return lb;
		}
		if (cursor == null) {
			lb = false;
		}

		if (cursor.moveToNext()) {
			lb = true;
		}

		cursor.close();
		return lb;
	}

	public boolean InsertPriceSetting(PriceSetting p) {

//		SQLiteDatabase db = DBHelper.getWritableDatabase();
		boolean check = true;
		String sql = "insert into t_price_setting values";
		sql += "('"
				+ p.getUpc()
				+ "',"
				+ p.getPriceMultiple()
				+ ","
				+ p.getPrice()
				+ ","
				+ getdatetime(p.getStartdate())
				+ ","
				+ gettime(p.getStarttime())
				+ ","
				+ getdatetime(p.getEnddate())
				+ ","
				+ gettime(p.getEndtime())
				+ ","
				+ p.getPackagepricemultiple()
				+ ","
				+ p.getPackageprice()
				+ ","
				+ p.getLimitedqtyamount()
				+ ","
				+ p.getLimitedqtyprice()
				+ ","
				+ p.getDiscountamount()
				+ ","
				+ p.getDiscountpercent()
				+ ","
				+ p.getPricemm()
				+ ",strftime('%Y%m%d%H%M%S','now'),strftime('%Y%m%d%H%M%S','now'),"
				+ p.getDislink()
				+ ","
				+ p.getPrice_lvl() + ")";

		errorString = DBHelper.execSQL(sql);
		if (errorString.length() > 0) {
			check = false;
		} 
		return check;
	}

	public boolean UpdatePriceSetting(PriceSetting p) {
//		SQLiteDatabase db = DBHelper.getWritableDatabase();
		boolean check = true;
		String sql = "update t_price_setting set ";
		sql += "F135 = " + p.getPriceMultiple() + "," + "F136 = "
				+ p.getPrice() + "," + "F137  = "
				+ getdatetime(p.getStartdate()) + "," + "F144  = "
				+ gettime(p.getStarttime()) + "," + "F138  = "
				+ getdatetime(p.getEnddate()) + "," + "F145  = "
				+ gettime(p.getEndtime()) + "," + "F143  = "
				+ p.getPackagepricemultiple() + "," + "F139  = "
				+ p.getPackageprice() + "," + "F147  = "
				+ p.getLimitedqtyamount() + "," + "F148  = "
				+ p.getLimitedqtyprice() + "," + "F111  = "
				+ p.getDiscountamount() + "," + "F112  = "
				+ p.getDiscountpercent() + "," + "F32  = " + p.getPricemm()
				+ "," + "ps_discnt_link   = " + p.getDislink()
				+ "," + "update_datetime = strftime('%Y%m%d%H%M%S','now')"
				+ "," + "ps_price_lvl = " + p.getPrice_lvl();

		sql += " where F01  = '" + p.getUpc() + "'";

		errorString = DBHelper.execSQL(sql);
		if (errorString.length() > 0) {
			check = false;
		} 
		return check;

	}
	
	public String getdatetime(String date){
		  
	  	Date curDate = null;
	  	String str;
		SimpleDateFormat  formatter  =  new  SimpleDateFormat("yyyyMMdd");
		DateFormat format1 = new SimpleDateFormat("yyyy-MM-dd");
		
		// Date curDate =(Date)date;
		try {
			if(date == null)return null;
			curDate = format1.parse(date);
			
		}catch(ParseException e) {
			e.printStackTrace();
		}
		if (curDate == null) {
			 str = null;
		}else{
			 str = formatter.format(curDate);
		}
		return "'"+str+"'";
	}
	
	public String gettime(String time){
	String str;
	if (time == null) {
		//str = null;
		return null;
	}else{
		str = time.replace(":","");
	}
	return "'"+str+"'";
	}
	
	
	public boolean DeletePriceSetting(String upc){
		String sql = "Delete from t_price_setting where F01 = '" + upc + "'";
		errorString = DBHelper.execSQL(sql);
		if (errorString.length() > 0) {
			return false;
		} 
		return true;
	}

	

}
